-- @owner: @nanyang12
-- @date: 2024/10/23
-- @testpoint:connect by基表为子查询和cte复合场景

--step1:建表
drop table if exists t_connect_by_0031_01,t_connect_by_0031_02;
create table t_connect_by_0031_01(id int, lid int, name text);
create table t_connect_by_0031_02(id int, lid int, name text);
--step2:插入数据
insert into t_connect_by_0031_01 values(1,null,'A'),(2,1,'B'),(3,2,'C');
insert into t_connect_by_0031_02 values(1,null,'A'),(2,1,'B'),(3,2,'C');
--step3:connect by查询
with t_connect_by_0031_02 as (select * from t_connect_by_0031_01 where id!=10) select level,t.* from
(select * from t_connect_by_0031_02 where id!=10 order by id) t start with t.id=2 connect by prior t.id=t.lid;
--step4:清理环境
drop table if exists t_connect_by_0031_01,t_connect_by_0031_02;
